#!/usr/bin/env python


import sys
from config import *
import logging
from errorCollator import *

LOGFILENAME = __file__ + ".log"
logging.basicConfig(format='%(asctime)s %(levelname)s: %(message)s',
      filename = LOGFILENAME, level=logging.INFO)

hiccups = ErrorCollator()



from ZSI import FaultException
from xmlMapping.dbConnect import *


class InstrumentWatcher:
  def __init__(self):
    self._log = logging.getLogger('%s.%s' % (__name__, self.__class__.__name__))

  @staticmethod
  def oracleEscape(self,strn):
    if not strn: return ''
    if strn=='none': return ''
    if strn=='None': return ''
    out = []
    for char in strn:
      if char =="'":
        out.append("'")
        out.append(char)
      else:
        out.append(char)
    ret = "".join(out)
    return ret

  def cleanup(self):
    if self.dbcnx:
      self.dbcnx.finish()
      self._log.info("Closed ICAT DB connection")

  def main(self):
    #populate remote ICAT FACILITY_USER table with these users
   
    self._log.info("Connecting: ICAT Oracle Database")
    bypass=False
    try:
      dbcnx = DBConnection(bypass)
      dbcnx.cxInit(ICATDBHOST, ICATDBPORT, ICATDBNAME, ICATDBUSER, ICATDBPASS)
      dbcnx.connect()     # establish tcp communication
    except Exception, e:
      self._log.critical(e)
      self._log.critical( "You may need to port forward over SSH to tunnel through firewall")
      self._log.critical( "e.g.   ssh dataminx -L 1521:localhost:1521")
      hiccups.add(Error(__name__,__file__,e) )
      return 2
    self._log.info("Remote connections established" )
    
    
    DBSCHEMA="ICAT"
    TABLE = 'INSTRUMENT'
    try:
        columns =dbcnx.query("""SELECT column_name FROM COLS
              WHERE table_name='%(TABLE)s'""" % 
                  {'DBSCHEMA' : DBSCHEMA, 'TABLE' : TABLE } )
    except Exception, msg:
        #import traceback
        #traceback.print_exc(file=sys.stdout)
        self._log.critical("Select header from %s failed\n%s" ,TABLE, msg)
        hiccups.add(Error(__name__,__file__,msg) )
        return 2
    
    header = []
    if not columns or columns.ntuples < 1:
      self._log.debug("no column headers exist")
    else:
      for row in columns:
        header.append(row[0])
    
    fileformats = []
    try:
        fields = ", ".join(header)
    #    print fields
        oldformats = dbcnx.query("""SELECT %(fields)s  FROM %(TABLE)s """ % 
                  {'DBSCHEMA' : DBSCHEMA ,'fields' : fields, 'TABLE' : TABLE } )
    except Exception, msg:
        #import traceback
        #traceback.print_exc(file=sys.stdout)
        self._log.critical("Select from %s failed\n%s" ,TABLE, msg)
        hiccups.add(Error(__name__,__file__,msg) )
        return 2
    if not oldformats or oldformats.ntuples < 1:
        self._log.debug("No %s entries exist", TABLE)
    else:
        for row in oldformats:
    #      print row
          d = {}
          for i in range(len(header)):
            d[header[i]] = row[i] 
          fileformats.append(d)
    
    
    for code in DataFormats.ARTIFACTS.keys():
      name = code
      short_name = name
      type = "Crystallography"
      description = " "  
      ftype = DataFormats.FILETYPES[code] 
      versions = ftype['version']
      if versions:
        for version in versions:
          match = False
          for itype in fileformats:
            if itype['NAME'] != ftype['format']: continue
            if itype['VERSION'] != version: continue
            match = True
            break
          if match: continue
          self._log.info("No current ICAT match for %s %s" , ftype['format'], version)
          self._log.info("Adding new entry ...")
          injectNewFileFormat(ftype,version)
           
      else:  # no version
          match = False
          for itype in fileformats:
            if itype['NAME'] != ftype['format']: continue
            match = True
            break
          if match: continue
          self._log.info("No  match for %s " , ftype['format'])
          injectNewFileFormat(ftype,'1')
    
    self._log.info("ICAT %s and config.py DataFormats are reconciled." , TABLE)
    self._log.info("Nothing else to add.")
        


  def injectNewFileFormat(self, format, version):
      try:
    #   (NAME, VERSION, FORMAT_TYPE, DESCRIPTION, SEQ_NUMBER, MOD_TIME, MOD_ID, CREATE_TIME, CREATE_ID, FACILITY_ACQUIRED, DELETED)
    # Values
    #   ('nexus', '3.0.0', 'HDF5', 'Neutron and X-Ray data format.', 999, TO_TIMESTAMP('12/09/2007 13:30:16.4','DD/MM/YYYY HH24:MI:SS.FF'), 'overlord', TO_TIMESTAMP('12/09/2007 13:30:16.4','DD/MM/YYYY HH24:MI:SS.FF'), 'overlord', 'Y', 'N');
    
        query= """
           INSERT INTO INSTRUMENT (NAME, SHORT_NAME, 
           TYPE, DESCRIPTION, SEQ_NUMBER, MOD_TIME, MOD_ID, 
           CREATE_TIME, CREATE_ID, FACILITY_ACQUIRED, DELETED) VALUES 
           ('%(name)s', '%(version)s', '%(type)s', '%(description)s', 
             999, systimestamp, 'overlord', systimestamp,'overlord', 'Y', 'N') """ % \
          {'DBSCHEMA' : DBSCHEMA, 
           'name': oracleEscape(format['format']), 
           'version': oracleEscape(version), 
           'type': oracleEscape(format['format_type']), 
           'description':oracleEscape(format['description']),  
          }  # )
        print query
        res = dbcnx.query(query)
        res = dbcnx.query("COMMIT")
      except Exception, msg:
        print "Failed to inject new  dataformat: ", format
        print  msg
        sys.exit()
      return


if __name__ == '__main__':
    watcher = InstrumentWatcher()
    try:
      rv = watcher.main()
    except Exception, msg:
      watcher._log.critical(msg)
    watcher.cleanup()
    import socket
    thisHost = socket.gethostbyaddr(socket.gethostname())
    fqdn = thisHost[1][0]
    if hiccups.hasErrors():
       hiccups.dump()
       hiccups.emailAlert("instrumentWatcher@"+fqdn, EMAIL_ALERT_LIST, EMAIL_SMTP_HOST,
                    "New ICAT instrument addition errors " + __file__ )


